<?php
/**
 * Description of Emms_Report_ActivePaymentsByFund
 *
 * @author andres
 */
class Emms_Report_ActivePaymentsByFund extends HTech_Report
{
    protected function _setName() 
    {
        $this->_name = $this->translate('Payments consolidated by fund');
    }

    protected function _setForm() 
    {
        $this->_form = new HTech_Report_Form_DateRange();
    }    
    
    protected function _setIndex() 
    {
        $this->_index = 'FundId';
    }

    protected function _setSql() 
    {
        $this->_sql = array();
        
        $this->_sql[] = '
            select 
                FundId,
                Fund,
                count(PaymentId) Payments,
                sum(Special) SpecialPayments,
                sum(if(Penalties>0,1,0)) DelinquentPayments,
                sum(Pmt) Pmt,
                sum(Principal) Principal,
                sum(LoanInsurance) LoanInsurance,
                sum(HealthInsurance) HealthInsurance,
                sum(AdministrativeFee) AdministrativeFee,
                sum(Interest) Interest,
                Sum(Penalties) Penalties,
                sum(Delay) Delay
            from 
                view_payments 
            where 
                Date >= :dateFrom and 
                Date <= :dateTo
            group by
                FundId
            order by
                FundId';
        
        $this->_sql[] = '
            select 
                FundId,
                count(PaymentId) BankPayments,
                sum(Special) BankSpecialPayments,
                sum(if(Penalties>0,1,0)) BankDelinquentPayments,
                sum(Pmt) BankPmt,
                sum(Principal) BankPrincipal,
                sum(LoanInsurance) BankLoanInsurance,
                sum(HealthInsurance) BankHealthInsurance,
                sum(AdministrativeFee) BankAdministrativeFee,
                sum(Interest) BankInterest,
                sum(Penalties) BankPenalties,
                sum(Delay) BankDelay
            from 
                view_payments 
            where 
                Date >= :dateFrom and 
                Date <= :dateTo and
                BorrowerType = "B"
            group by
                FundId
            order by
                FundId';
        
        $this->_sql[] = '
            select 
                FundId,
                count(PaymentId) IndividualPayments,
                sum(Special) IndividualSpecialPayments,
                sum(if(Penalties>0,1,0)) IndividualDelinquentPayments,
                sum(Pmt) IndividualPmt,
                sum(Principal) IndividualPrincipal,
                sum(LoanInsurance) IndividualLoanInsurance,
                sum(HealthInsurance) IndividualHealthInsurance,
                sum(AdministrativeFee) IndividualAdministrativeFee,
                sum(Interest) IndividualInterest,
                sum(Penalties) IndividualPenalties,
                sum(Delay) IndividualDelay
            from 
                view_payments 
            where 
                Date >= :dateFrom and 
                Date <= :dateTo and
                BorrowerType = "I"
            group by
                FundId
            order by
                FundId';
    }
    
    protected function _setHeaders()
    {
        $this->_headers = new stdClass();
        $this->_headers->Fund = $this->translate('Fund');
        $this->_headers->Payments = $this->translate('Payments');
        $this->_headers->SpecialPayments = $this->translate('Special payments');
        $this->_headers->DelinquentPayments = $this->translate('Delinquent payments');
        $this->_headers->Pmt = $this->translate('Amount');
        $this->_headers->Principal = $this->translate('Principal');
        $this->_headers->LoanInsurance = $this->translate('Loan insurance');
        $this->_headers->HealthInsurance = $this->translate('Health insurance');
        $this->_headers->AdministrativeFee = $this->translate('Admin. fee');
        $this->_headers->Interest = $this->translate('Interest');
        $this->_headers->Penalties = $this->translate('Penalties');
        $this->_headers->Delay = $this->translate('Delay');
        $this->_headers->BankPayments = $this->translate('B-Payments');
        $this->_headers->BankSpecialPayments = $this->translate('B-Special payments');
        $this->_headers->BankDelinquentPayments = $this->translate('B-Delinquent payments');
        $this->_headers->BankPmt = $this->translate('B-Amount');
        $this->_headers->BankPrincipal = $this->translate('B-Principal');
        $this->_headers->BankLoanInsurance = $this->translate('B-Loan insurance');
        $this->_headers->BankHealthInsurance = $this->translate('B-Health insurance');
        $this->_headers->BankAdministrativeFee = $this->translate('B-Admin. fee');
        $this->_headers->BankInterest = $this->translate('B-Interest');
        $this->_headers->BankPenalties = $this->translate('B-Penalties');
        $this->_headers->BankDelay = $this->translate('B-Delay');
        $this->_headers->IndividualPayments = $this->translate('I-Payments');
        $this->_headers->IndividualSpecialPayments = $this->translate('I-Special payments');
        $this->_headers->IndividualDelinquentPayments = $this->translate('I-Delinquent payments');
        $this->_headers->IndividualPmt = $this->translate('I-Amount');
        $this->_headers->IndividualPrincipal = $this->translate('I-Principal');
        $this->_headers->IndividualLoanInsurance = $this->translate('I-Loan insurance');
        $this->_headers->IndividualHealthInsurance = $this->translate('I-Health insurance');
        $this->_headers->IndividualAdministrativeFee = $this->translate('I-Admin. fee');
        $this->_headers->IndividualInterest = $this->translate('I-Interest');
        $this->_headers->IndividualPenalties = $this->translate('I-Penalties');
        $this->_headers->IndividualDelay = $this->translate('I-Delay');
    }
    
    public function setParams($params) 
    {
        $this->_params = array(
            ':dateFrom' => $params['DateFrom'],
            ':dateTo' => $params['DateTo']);
    }

}
